/*
 Navicat Premium Data Transfer

 Source Server         : 本机SQL Server
 Source Server Type    : SQL Server
 Source Server Version : 13001742
 Source Host           : localhost:1433
 Source Catalog        : Hotel
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 13001742
 File Encoding         : 65001

 Date: 04/01/2022 20:04:35
*/


-- ----------------------------
-- Table structure for t_order
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_order]') AND type IN ('U'))
	DROP TABLE [dbo].[t_order]
GO

CREATE TABLE [dbo].[t_order] (
  [id] int  IDENTITY(1,1) NOT NULL,
  [room_id] int  NOT NULL,
  [sdate] datetime  NULL,
  [edate] datetime  NULL,
  [discount] int  NOT NULL,
  [price] decimal(18,2)  NULL,
  [customer_name] varchar(255) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [customer_cardid] varchar(30) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [customer_phone] varchar(20) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [remark] varchar(255) COLLATE Chinese_PRC_CI_AS  NOT NULL
)
GO

ALTER TABLE [dbo].[t_order] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of t_order
-- ----------------------------
SET IDENTITY_INSERT [dbo].[t_order] ON
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'1', N'1', N'2021-10-01 18:43:30.640', N'2021-12-16 02:12:58.967', N'100', N'6800.00', N'张三', N'371111201111111234', N'13322334455', N'备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'2', N'1', N'2021-10-04 18:43:40.730', N'2021-10-05 08:43:40.730', N'100', N'68.00', N'张三', N'371111201111111234', N'13322334455', N'备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'3', N'1', N'2021-10-07 18:43:41.400', N'2021-10-08 09:43:41.400', N'100', N'68.00', N'张三', N'371111201111111234', N'13322334455', N'备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'4', N'1', N'2021-12-01 18:43:42.040', N'2021-12-02 08:43:42.040', N'100', N'68.00', N'张三', N'371111201111111234', N'13322334455', N'备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'5', N'1', N'2021-12-03 18:43:42.537', N'2021-12-04 10:43:42.553', N'100', N'68.00', N'张三', N'371111201111111234', N'13322334455', N'备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'6', N'16', N'2021-11-01 18:44:53.193', N'2021-11-02 10:44:53.210', N'100', N'106.92', N'李四', N'370000200000001234', N'10000000000', N'vip订房备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'7', N'16', N'2021-11-06 18:44:54.710', N'2021-11-07 08:44:54.710', N'100', N'104.76', N'李四', N'370000200000001234', N'10000000000', N'vip订房备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'8', N'16', N'2021-11-09 18:44:56.533', N'2021-11-10 11:44:56.550', N'100', N'104.76', N'李四', N'370000200000001234', N'10000000000', N'vip订房备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'9', N'16', N'2021-12-05 18:44:57.000', N'2021-12-06 11:44:57.000', N'100', N'104.76', N'李四', N'370000200000001234', N'10000000000', N'vip订房备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'10', N'16', N'2021-12-06 12:44:57.550', N'2021-12-07 11:44:57.550', N'100', N'104.76', N'李四', N'370000200000001234', N'10000000000', N'vip订房备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'11', N'16', N'2021-12-15 20:43:01.220', N'2021-12-16 02:30:49.150', N'100', N'108.00', N'李四', N'370000200000001234', N'10000000000', N'vip订房备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'12', N'1', N'2021-12-15 20:43:05.387', N'2021-12-16 02:23:37.010', N'100', N'68.00', N'张三', N'371111201111111234', N'13322334455', N'备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'16', N'5', N'2021-12-15 23:08:37.140', N'2021-12-16 02:30:25.280', N'99', N'67.32', N'老八', N'221237298810011221', N'19400001111', N'来点老八秘制小汉堡')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'17', N'6', N'2021-12-16 00:23:00.023', N'2021-12-16 02:30:32.090', N'100', N'88.00', N'孙', N'1248718924235', N'124868124', N'奥利给！！！')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'18', N'7', N'2021-12-16 00:26:18.840', N'2021-12-16 02:30:38.350', N'99', N'87.12', N'老八', N'221237298810011221', N'19400001111', N'v备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'19', N'11', N'2021-12-16 00:38:05.387', N'2021-12-16 02:30:42.313', N'100', N'88.00', N'术栋', N'1234567891234567', N'12345678901', N'无备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'20', N'1', N'2021-12-16 02:32:47.340', N'2021-12-16 02:32:50.153', N'95', N'64.60', N'李四', N'370000200000001234', N'10000000000', N'v备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'21', N'20', N'2021-12-16 16:49:16.003', N'2021-12-16 16:49:22.597', N'97', N'104.76', N'老八', N'221237298810011221', N'19400001111', N'v备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'22', N'1', N'2021-12-16 16:53:09.433', N'2021-12-16 16:53:11.860', N'95', N'64.60', N'李四', N'370000200000001234', N'10000000000', N'v备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'23', N'1', N'2021-12-16 16:53:27.783', N'2021-12-16 16:53:30.070', N'95', N'64.60', N'李四', N'370000200000001234', N'10000000000', N'v备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'24', N'1', N'2021-12-16 19:10:06.837', N'2021-12-16 19:10:09.617', N'95', N'64.60', N'李四', N'370000200000001234', N'10000000000', N'v备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'25', N'1', N'2021-12-16 19:13:23.217', N'2021-12-16 19:13:25.867', N'95', N'64.60', N'李四', N'370000200000001234', N'10000000000', N'v备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'1011', N'1', N'2021-12-16 20:26:48.697', N'2021-12-16 20:27:41.397', N'95', N'64.60', N'李四', N'370000200000001234', N'10000000000', N'v备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'1012', N'11', N'2021-12-16 20:27:07.087', N'2021-12-16 20:27:48.167', N'95', N'83.60', N'李四', N'370000200000001234', N'10000000000', N'v备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'1013', N'14', N'2021-12-16 20:27:30.667', N'2021-12-16 20:27:53.647', N'97', N'85.36', N'老八', N'221237298810011221', N'19400001111', N'奥利给备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'1014', N'1', N'2021-12-16 20:55:15.860', N'2021-12-16 20:55:27.777', N'90', N'61.20', N'李四', N'370000200000001234', N'10000000000', N'v备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'1015', N'1', N'2021-12-17 15:03:42.393', N'2021-12-17 19:35:16.977', N'99', N'67.32', N'v1', N'172612535182491', N'7125182481', N'v备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'1016', N'1', N'2021-12-19 20:47:42.213', N'2021-12-19 20:48:04.070', N'100', N'38.00', N'张三', N'371111201111111234', N'13322334455', N'备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'1017', N'21', N'2021-12-19 20:48:59.100', N'2021-12-19 20:49:16.290', N'100', N'77.22', N'李11四', N'110000200000001234', N'1011111000', N'vip订房备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'1018', N'12', N'2021-12-19 21:00:37.653', N'2021-12-19 21:01:31.093', N'99', N'67.32', N'李11四', N'110000200000001234', N'1011111000', N'v备注')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'1019', N'26', N'2021-12-19 21:47:14.567', N'2021-12-19 21:49:38.863', N'100', N'98.00', N'姓名', N'123243241234', N'124343123', N'1111122222')
GO

INSERT INTO [dbo].[t_order] ([id], [room_id], [sdate], [edate], [discount], [price], [customer_name], [customer_cardid], [customer_phone], [remark]) VALUES (N'1020', N'27', N'2021-12-19 21:49:02.783', N'2021-12-19 21:49:54.677', N'99', N'97.02', N'葱香', N'123567890234567', N'1212312311', N'v备注')
GO

SET IDENTITY_INSERT [dbo].[t_order] OFF
GO


-- ----------------------------
-- Auto increment value for t_order
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[t_order]', RESEED, 1020)
GO


-- ----------------------------
-- Triggers structure for table t_order
-- ----------------------------
CREATE TRIGGER [dbo].[bill_update_status]
ON [dbo].[t_order]
WITH EXECUTE AS CALLER
FOR UPDATE
AS
begin
	declare @sdate datetime, @edate datetime, @roomid int
	select @sdate=sdate,@edate=edate from deleted
	if @sdate is not null and @edate is null
	begin
		select @edate=edate,@roomid=room_id from inserted
		if @edate is not null
		begin
			update t_room set status='空闲' where id=@roomid
		end
	end
end
GO

CREATE TRIGGER [dbo].[order_update_status]
ON [dbo].[t_order]
WITH EXECUTE AS CALLER
FOR INSERT
AS
begin
	declare @roomid int
	select @roomid=room_id from inserted
	if @roomid is not null
	begin
		update t_room set status='有客' where id=@roomid
	end
end
GO

CREATE TRIGGER [dbo].[vip_points_add]
ON [dbo].[t_order]
WITH EXECUTE AS CALLER
FOR UPDATE
AS
begin
	declare @price decimal,@price2 decimal,@cardid varchar(30)
	select @price=price from deleted
	if @price is null
	begin
		select @cardid=customer_cardid,@price2=price from inserted
		if @price2 is not null
		begin
			update t_vip set points=points+@price2 where cardid=@cardid
		end
	end
end
GO


-- ----------------------------
-- Primary Key structure for table t_order
-- ----------------------------
ALTER TABLE [dbo].[t_order] ADD CONSTRAINT [PK__t_order__id] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Foreign Keys structure for table t_order
-- ----------------------------
ALTER TABLE [dbo].[t_order] ADD CONSTRAINT [FK__t_order__room_id] FOREIGN KEY ([room_id]) REFERENCES [dbo].[t_room] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

